<?php
/**
 * (exporter) 备货需求（总表）
 */
namespace App\Exporter\ProductStockup;

use App\Models\Product\ProductClassify;
use App\Models\ProductStockup\Stockup;
use App\Models\SsBasic\StockupAreaSkuEstimate;
use Encore\Admin\Grid\Exporters\ExcelExporter;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;

class StockupExporter extends ExcelExporter implements WithMapping,WithHeadings{

    protected $fileName = '产品备货（新）.csv';

    public function headings(): array{

        $head1 = ['品类', '站点', 'SKU编号', 'SKU名称', '推荐备货数量', '申请数量', '申请备注', '审批数量', '审批备注', '确认数量', '确认备注', '备货状态'];
        $head2 = ['3天AVG', '7天AVG', '15天AVG', '30天AVG', 'AVG'];
        $head3 = ['最近旺季销售比例', '最近3天平均销量', '最近30天平均销量', '最近30天销售比例', '确定后的销售比例'];
        $head4 = ['FBA在库', 'FBA在途', '国内在库', '国内在途', '海外仓在库', '海外仓在途', '分配数量', '分配剩余', '库存总量'];

        $head5 = [];
        for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $head5[] = date('y/m',strtotime("midnight first day of $i month")).'预估';
        }
        $head51 = ['预估总量', '同期增长率'];

        $head6 = ['公共在库数量', '中国库存总量', '备货推荐和总积压库存', '总库存可挪用积压', '中国在库可挪用积压', '中国在途可挪用积压', '阶段性可挪用在库存', '推荐海外仓发货数量', '实际海外仓发货数量'];
        $head7 = [];
        for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $head7[] = date('y/m',strtotime("midnight first day of $i month")).'中国发货';
        }
        $head8 = [];
        for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $head8[] = date('y/m',strtotime("midnight first day of $i month")).'需求库存';
        }

        return array_merge($head1,$head2,$head3,$head4,$head5,$head51,$head6,$head7,$head8);
    }

    /*function getExportDataByTitle($title, $fileData)
    {
        $titleFlip = array_flip(array_values(array_flip($title)));
        $result = [];
        $index = 1;
        foreach ($fileData as $key => $row) {
            foreach ($titleFlip as $col => $i) {
                if (stripos($col, '.')) {
                    $allKey = explode('.', $col);
                    $res = $row;
                    foreach ($allKey as $k) {
                        $res = $res[$k] ?? null;
                    }
                    $result[$key][$i] = $res;
                } else {
                    if (isset($row[$col])) {
                        $value = $row[$col];
                        $result[$key][$i] = $value;
                    } else {
                        $result[$key][$i] = '';
                    }
                    if ($col == 'index') $result[$key][$i] = (string)$index;
                }
            }
            $index++;
        }
        array_unshift($result, array_values($title));
        return $result;
    }*/

    public function map($model) : array{

        $data1 = [
            $model->classify->title ?? '',
            '',
            '',
            '',
            Stockup::getStockupColumnSum($model->classify_id, 'recommend_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'apply_num'),
            '',
            Stockup::getStockupColumnSum($model->classify_id, 'approval_num') ?? 0,
            '',
            Stockup::getStockupColumnSum($model->classify_id, 'confirm_num') ?? 0,
            '',
            ''
        ];
        $data2 = [
            Stockup::getStockupColumnSum($model->classify_id, 'avg3') ?: 0,
            Stockup::getStockupColumnSum($model->classify_id, 'avg7') ?: 0,
            Stockup::getStockupColumnSum($model->classify_id, 'avg15') ?: 0,
            Stockup::getStockupColumnSum($model->classify_id, 'avg30') ?: 0,
            Stockup::getStockupColumnSum($model->classify_id, 'avg') ?: 0
        ];
        $data3 = [
            Stockup::getStockupColumnSum($model->classify_id, 'current_sale_ratio'),
            Stockup::getStockupColumnSum($model->classify_id, 'volume3_avg'),
            Stockup::getStockupColumnSum($model->classify_id, 'volume30_avg'),
            Stockup::getStockupColumnSum($model->classify_id, 'volume30_ratio'),
            Stockup::getStockupColumnSum($model->classify_id, 'ratio')
        ];
        $data4 = [
            Stockup::getStockupColumnSum($model->classify_id, 'fba_instant_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'fba_transit_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'cn_instant_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'cn_transit_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'os_instant_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'os_transit_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'dispense_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'dispense_surplus_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'stock_total') ?? 0,
        ];

        $data5 = [];
        for ($i=0; $i<StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $data5[] = Stockup::getStockupColumnSum($model->classify_id, 'estimate_num'.$i) ?: 0;
        }

        $data5 = [];
        for ($i=0; $i<StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $data5[] = Stockup::getStockupColumnSum($model->classify_id, 'cn_delivery_num'.$i) ?: 0;
        }
        $data51 = [
            Stockup::getStockupColumnSum($model->classify_id, 'estimate_total') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'estimate_growth_rate') ?? 0,
        ];

        $data6 = [];
        for ($i=0; $i<StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $data6[] = Stockup::getStockupColumnSum($model->classify_id, 'stock_plan'.$i) ?: 0;
        }
        $data7 = [
            Stockup::getStockupColumnSum($model->classify_id, 'cn_pulic_instant_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'cn_stock_total') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'overstock_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'divert_overstock_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'divert_instant_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'divert_transit_num') ?? 0,
            Stockup::getStockupColumnSum($model->classify_id, 'defer_stock_num') ?? 0
        ];

        $count = 0;
        $data = [];
        $data[$count] = array_merge($data1,$data2,$data3,$data4,$data5);

        //品类下的sku
        $classifySku = Stockup::getClassifySku($model->classify_id);
        foreach($classifySku as $item){
            $count++;
            $classifySku1 = [
                '',
                '',
                $item->sku->psku_code ?? '',
                $item->sku->psku_cn ?? '',
                $item->recommend_num ?? 0,
                $item->apply_num ?? 0,
                $item->apply_remark ?? '',
                $item->approval_num ?? 0,
                $item->approval_remark ?? '',
                $item->confirm_num ?? 0,
                $item->confirm_remark ?? '',
                Stockup::$APPLY_STATUS[$item->status] ?? '',
                $item->avg3 ?: 0,
                $item->avg7 ?: 0,
                $item->avg15 ?: 0,
                $item->avg30 ?: 0,
                $item->avg ?: 0
            ];
            $classifySku2 = [];
            for ($i=0; $i<StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
                $classifySku2[] = $item->{"estimate_num$i"} ?: 0;
            }
            $classifySku3 = [];
            for ($i=0; $i<StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
                $classifySku3[] = $item->{"cn_delivery_num$i"} ?: 0;
            }
            $classifySku4 = [];
            for ($i=0; $i<StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
                $classifySku4[] = $item->{"stock_plan$i"} ?: 0;
            }

            $classifySku5 = [
                $item->fba_instant_num ?? 0,
                $item->fba_transit_num ?? 0,
                $item->cn_instant_num ?? 0,
                $item->cn_transit_num ?? 0,
                $item->os_instant_num ?? 0,
                $item->os_transit_num ?? 0,
                $item->stock_total ?? 0,
                $item->cn_pulic_instant_num ?? 0,
                $item->cn_stock_total ?? 0,
                $item->overstock_num ?? 0,
                $item->divert_overstock_num ?? 0,
                $item->divert_instant_num ?? 0,
                $item->divert_transit_num ?? 0,
                $item->defer_stock_num ?? 0
            ];
            $data[$count] = array_merge($classifySku1,$classifySku2,$classifySku3,$classifySku4,$classifySku5);
        }

        //品类下的站点
        $classifyArea = Stockup::getClassifyArea($model->classify_id);
        foreach($classifyArea as $val) {
            $count++;
            $classifyArea1 = [
                '',
                $val->area->area ?? '',
                '',
                '',
                $val->recommend_num ?? 0,
                $val->apply_num ?? 0,
                $val->apply_remark ?? '',
                $val->approval_num ?? 0,
                $val->approval_remark ?? '',
                $val->confirm_num ?? 0,
                $val->confirm_remark ?? '',
                '',
                $val->avg3 ?: 0,
                $val->avg7 ?: 0,
                $val->avg15 ?: 0,
                $val->avg30 ?: 0,
                $val->avg ?: 0,
            ];
            $classifyArea2 = [];
            for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++) {
                $classifyArea2[] = $val->{"estimate_num$i"} ?: 0;
            }
            $classifyArea3 = [];
            for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++) {
                $classifyArea3[] = $val->{"cn_delivery_num$i"} ?: 0;
            }
            $classifyArea4 = [];
            for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++) {
                $classifyArea4[] = $val->{"stock_plan$i"} ?: 0;
            }
            $classifyArea5 = [
                $val->fba_instant_num ?? 0,
                $val->fba_transit_num ?? 0,
                $val->cn_instant_num ?? 0,
                $val->cn_transit_num ?? 0,
                $val->os_instant_num ?? 0,
                $val->os_transit_num ?? 0,
                $val->stock_total ?? 0,
                $val->cn_pulic_instant_num ?? 0,
                $val->cn_stock_total ?? 0,
                $val->overstock_num ?? 0,
                $val->divert_overstock_num ?? 0,
                $val->divert_instant_num ?? 0,
                $val->divert_transit_num ?? 0,
                $val->defer_stock_num ?? 0
            ];
            $data[$count] = array_merge($classifyArea1,$classifyArea2,$classifyArea3,$classifyArea4,$classifyArea5);

            //品类站点下的sku
            $classifyAreaSku = Stockup::getClassifyAreaSku($val->classify_id, $val->area_id);
            foreach ($classifyAreaSku as $k=>$v) {
                $count++;
                $classifyAreaSku1 = [
                    '',
                    '',
                    $v->sku->psku_code ?? '',
                    $v->sku->psku_cn ?? '',
                    $v->recommend_num ?? 0,
                    $v->apply_num ?? 0,
                    $v->apply_remark ?? '',
                    $v->approval_num ?? 0,
                    $v->approval_remark ?? '',
                    $v->confirm_num ?? 0,
                    $v->confirm_remark ?? '',
                    Stockup::$APPLY_STATUS[$v->status] ?? '',
                    $v->avg3 ?: 0,
                    $v->avg7 ?: 0,
                    $v->avg15 ?: 0,
                    $v->avg30 ?: 0,
                    $v->avg ?: 0,
                ];
                $classifyAreaSku2 = [];
                for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++) {
                    $classifyAreaSku2[] = $v->{"estimate_num$i"} ?: 0;
                }
                $classifyAreaSku3 = [];
                for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++) {
                    $classifyAreaSku3[] = $v->{"cn_delivery_num$i"} ?: 0;
                }
                $classifyAreaSku4 = [];
                for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++) {
                    $classifyAreaSku4[] = $v->{"stock_plan$i"} ?: 0;
                }
                $classifyAreaSku5 = [
                    $v->fba_instant_num ?? 0,
                    $v->fba_transit_num ?? 0,
                    $v->cn_instant_num ?? 0,
                    $v->cn_transit_num ?? 0,
                    $v->os_instant_num ?? 0,
                    $v->os_transit_num ?? 0,
                    $v->stock_total ?? 0,
                    $v->cn_pulic_instant_num ?? 0,
                    $v->cn_stock_total ?? 0,
                    $v->overstock_num ?? 0,
                    $v->divert_overstock_num ?? 0,
                    $v->divert_instant_num ?? 0,
                    $v->divert_transit_num ?? 0,
                    $v->defer_stock_num ?? 0,
                ];
                $data[$count] = array_merge($classifyAreaSku1,$classifyAreaSku2,$classifyAreaSku3,$classifyAreaSku4,$classifyAreaSku5);
            }
        }

        return $data;
    }

    public function setExportColumn(){

        $head1 = [
            'classify_id' => '品类',
            'area_id' => '站点',
            'psku_id' => 'SKUID',
            'psku_code' => 'SKU编号',
            'psku_cn' => 'SKU名称',
            'recommend_num' => '推荐备货数量',
            '' => '申请数量',
            '' => '申请备注',
            '' => '审批数量',
            '' => '审批备注',
            '' => '确认数量',
            '' => '确认备注',
            '' => '备货状态'
        ];
        $head2 = ['3天AVG', '7天AVG', '15天AVG', '30天AVG', 'AVG'];
        $head3 = ['最近旺季销售比例', '最近3天平均销量', '最近30天平均销量', '最近30天销售比例', '确定后的销售比例'];
        $head4 = ['FBA在库', 'FBA在途', '国内在库', '国内在途', '海外仓在库', '海外仓在途', '分配数量', '分配剩余', '库存总量'];

        $head5 = [];
        for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $head5[] = date('y/m',strtotime("midnight first day of $i month")).'预估';
        }
        $head51 = ['预估总量', '同期增长率'];

        $head6 = ['公共在库数量', '中国库存总量', '备货推荐和总积压库存', '总库存可挪用积压', '中国在库可挪用积压', '中国在途可挪用积压', '阶段性可挪用在库存', '推荐海外仓发货数量', '实际海外仓发货数量'];
        $head7 = [];
        for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $head5[] = date('y/m',strtotime("midnight first day of $i month")).'中国发货';
        }
        $head8 = [];
        for ($i = 0; $i < StockupAreaSkuEstimate::NEW_ESTIMATE_NUM; $i++){
            $head6[] = date('y/m',strtotime("midnight first day of $i month")).'需求库存';
        }

        return array_merge($head1,$head2,$head3,$head4,$head5,$head51,$head6,$head7,$head8);
    }

}
